# ----商超管理系统（MySQL版）---
# 使用MySQL保存数据，实现超市日常管理中以下业务。
#
# 一、建立以下数据表并填充数据
# 产品表：products
# 列名：主键id  编号num  名称name  单价price  折扣discount
#
# 订单表：orders
# 列名：主键id  编号num  货品数量count  金额amount
#
# 二、实现以下菜单并实现业务:
# 1.查看所有商品信息；
# 2.查找商品（用户输入商品名称，查看价格和折扣）；
# 3.添加商品（id需要程序生成）；
# 4.修改商品价格和折扣（根据编号，设置折扣为0-1之间）；
# 5.删除商品（根据编号）；
# 6.查看所有订单；
# 7.删除订单（根据编号）；
# 8.订单统计（总销量，总销售额）；
# 9.商品结算（添加订单）。
import orm
def show_products():
    sql=f"""
        select num,name,price,discount from products
    """
    data=orm.reaf_data(sql)
    if data !=():
        print('-编码---名称-价格-折扣--')
        for product in data:
            print(f'{product[0]}\t{product[1]}\t{product[2]}\t{product[3]}')

def get_product():
    num=input("请输入查询商品的编码：")
    sql = f"""
        select num,name,price,discount from products where num='{num}'       
    """
    data =orm.reaf_data(sql)
    if data !=():
        print('-编码---名称-价格-折扣--')
        for product in data:
            print(f'{product[0]}\t{product[1]}\t{product[2]}\t{product[3]}')
    else:
        print('商品不存在请重新输入！')
    return data

def add_product():
    num=int(input("请输入商品的编号："))
    name = input("请输入商品的名称：")
    price = float(input("请输入商品的价格："))
    discount =float(input("请输入商品的折扣："))

    sql = f"""
        INSERT INTO `market`.`products`(`id`, `num`, `name`, `price`, `discount`) VALUES (null, {num}, '{name}', {price}, {discount});    
    """
    sql1 = f"""
        select num,name,price,discount from products    
    """
    rows=orm.write_data(sql)
    data =orm.reaf_data(sql1)
    if data !=():
        print('-编码---名称-价格-折扣--')
        for product in data:
            print(f'{product[0]}\t{product[1]}\t{product[2]}\t{product[3]}')
    return rows


def mod_product():

    num = int(input("请输入商品的编号："))
    price = float(input("请输入商品的价格："))
    print('请输入折扣在0-1之间的数')
    discount = float(input("请输入商品的折扣："))

    sql = f"""
            update products set price={price},discount={discount} where num={num}    
        """
    sql1 = f"""
            select num,name,price,discount from products    
        """
    rows = orm.write_data(sql)
    data = orm.reaf_data(sql1)
    if data != ():
        print('-编码---名称-价格-折扣--')
        for product in data:
            print(f'{product[0]}\t{product[1]}\t{product[2]}\t{product[3]}')
    return rows

def del_product():
    num = int(input("请删除商品的编号："))
    sql = f"""
            delete from products where num={num}
        """
    sql1 = f"""
            select num,name,price,discount from products    
        """
    rows = orm.write_data(sql)
    data = orm.reaf_data(sql1)
    if data != ():
        print('-编码---名称-价格-折扣--')
        for product in data:
            print(f'{product[0]}\t{product[1]}\t{product[2]}\t{product[3]}')
    return rows

def show_orders():
    sql = f"""
            select id,num,count,amount from orders
        """
    data = orm.reaf_data(sql)
    if data != ():
        print('-编号---数量--总价--')
        for orders in data:
            print(f'{orders[0]}\t{orders[1]}\t{orders[2]}')


def del_order():
    num = int(input("请输入删除订单的编号："))
    sql = f"""
                delete from orders where num={num}
            """
    sql1 = f"""
                select num,count,amount from orders 
            """
    rows = orm.write_data(sql)
    data = orm.reaf_data(sql1)
    if data != ():
        print('-编码---名称-价格-折扣--')
        for orders in data:
            print(f'{orders[0]}\t{orders[1]}\t{orders[2]}')
    return rows


def count_orders():
    sql = f"""
            select sum(count), sum(amount) from orders
            """
    sql1 = f"""
                select num,count,amount from orders 
            """
    rows = orm.write_data(sql)
    data = orm.reaf_data(sql1)
    print(f"总销量：{data[0][0]}，总销售额：{data[0][1]}")
    if data != ():
        print('-编码---名称-价格-折扣--')
        for orders in data:
            print(f'{orders[0]}\t{orders[1]}\t{orders[2]}')
    return rows


def add_orders():
    amount=0
    count=0
    while True:
        data = get_product()
        if data !=():
            num=int(input("请输入要购买的数量"))
            monery = data[0][2]
            count += num
            amount+=monery
            choice=int(input('请输入你的选择：1继续 2退出：'))
            if choice ==1:
                continue
            else:
                break
    print(f"总数{count},金额{amount}")
    sql=f"""
        select max(num) from orders
    """
    data=orm.write_data(sql)
    id=data[0][0]+1
    sql=f"""
        insert into orders values(null,{id},{count},{amount})
    """
    rows=orm.write_data(sql)
    if rows>0:
        print("订单写入成功")
    else:
        print("订单写入失败")

    # num = int(input("请输入订单的编号："))
    # count = float(input("请输入订单的数量："))
    # amount = float(input("请输入订单的金额："))

    # sql = f"""
    #         INSERT INTO `market`.`orders`(`id`, `num`, `count`, `amount`) VALUES (null, {num}, {count}, {amount});
    #     """
    # sql1 = f"""
    #         select num,count,amount from orders
    #     """
    # rows = orm.write_data(sql)
    # data_discuz = orm.reaf_data(sql1)
    # if data_discuz != ():
    #     print('-编码---名称-价格-折扣--')
    #     for orders in data_discuz:
    #         print(f'{orders[0]}\t{orders[1]}\t{orders[2]}')
    # return rows


if __name__ == '__main__':
    # 测试一下
    # show_products()
    # get_product()
    # add_product()
    # mod_product()
    # del_product()
    # show_orders()
    # del_order()
    # count_orders()
    add_orders()